from xmindparser import xmind_to_dict
import sys
import re
import xlwt
import json
import pandas as pd
from datetime import datetime, timedelta
import win32com.client as win32
import os.path
from pathlib import Path
import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox

class xmind_to_excel():
    def xmind_num(self, value):
        '''
        获取xmind标题个数
        '''
        try:
            return len(value['topics'])
        except KeyError:
            return 0
    def xmind_title(self, value):
        '''
        获取xmind标题内容
        '''
        return value['title']
    def xmind_test(self, filename):
        '''
        调试函数，打印内容用的
        '''
        self.out = xmind_to_dict(filename)
        self.story = self.out[0]['topic']['topics']
        self.num = len(self.story)
        print('>>>解析xmind生成的字典>>>', json.dumps(self.out, indent=1, ensure_ascii=False))
        print('>>>根节点：>>>', self.out[0]['topic']['title'])
        return self.story, self.num
    def write_excel(self, xmind_file, creator='', function_module='', case_list='', excel_path='' ):
        '''
        xmind文件转换成excel文件函数
        :param xmind_file: xmind 文件的绝对路径；
        :param case_list: TAPD的用例目录
        :param creator: 用例的创建人
        :param function_module: 用例所属功能模块
        :return:
        0需求ID story_id；1用例目录case_list；2功能模块function_module；3用例名称testcase_name；4是否冒烟run；5前置条件precondition；
        6用例步骤testcase_step；7预期结果test_result；8用例类型case_type；9用例状态case_statu；10用例等级case_level；11创建人creator
        '''
        # 以下列的值直接写死
        run = '是'  # 是否冒烟4
        case_type = '功能测试'  # 用例类型8
        case_statu = '正常'  # 用例状态9
        case_level = '中'  # 用例等级10

        self.f = xlwt.Workbook()
        # excel文件生成sheet1页命名为TestCases
        self.sheet1 = self.f.add_sheet('TestCases', cell_overwrite_ok=True)  # 第二参数用于确认同一个cell单元是否可以重设值
        # 生成第一行
        self.row0 = ['需求编号', '用例目录', '功能模块', '用例名称', '是否冒烟', '前置条件', '用例步骤', '预期结果', '用例类型', '用例状态', '用例等级', '创建人']
        for i in range(0, len(self.row0)):
            self.sheet1.write(0, i, self.row0[i])
        # 将xmind打开并转换为dict类型
        self.out = xmind_to_dict(xmind_file)
        # print('>>>解析xmind生成的字典输出成json>>>', json.dumps(self.out, indent=1, ensure_ascii = False))
        # 根节点名称当作excel名称
        # out[0]['topic']为根节点位置，out[0]['topic']['title']意思是取根节点下的title,即为根节点标题
        self.xls_name = self.out[0]['topic']['title']
        # 需求编号及其后边的步骤
        # out[0]['topic']为根节点位置，out[0]['topic']['topics']意思是取根节点下的topics,即为根节点下属分支
        self.story = self.out[0]['topic']['topics']
        # 用例计数器
        j = 1
        for i in range(0, len(self.story)):
            # story[1]为根节点下属分支1，story[1]['title']即为根节点下属分支1的标题，即为“需求编号列_1”
            self.story_id = self.story[i]['title']  # 需求ID取值
            print('----------------------------该需求编号:%s----------------------------' % (self.story_id))
            if "topics" in self.story[i].keys():
                self.testcase_num = len(self.story[i]['topics'])
                print('>>>该需求下有%s条用例:>>>' % (self.testcase_num))
            for k in range(0, self.testcase_num):
                if "topics" in self.story[i].keys():
                    self.testcase_name = self.story[i]['topics'][k]['title']  # 用例名称取值
                    print('>>>%s' % (self.testcase_name))
                    self.testcase = self.story[i]['topics'][k]['topics'][0]  # self.story[i]['topics'][k] 表示当前需求下的第一条用例
                    self.precondition = self.xmind_title(self.testcase)  # 前置条件取值
                    self.testcase_step = self.xmind_title(self.testcase['topics'][0])  # 用例步骤取值
                    self.test_result = self.xmind_title(self.testcase['topics'][0]['topics'][0])  # 预期结果取值
                # 往excel写值
                self.sheet1.write(j, 0, self.story_id)  # 需求ID√
                self.sheet1.write(j, 1, case_list)  # 用例目录
                self.sheet1.write(j, 2, function_module)  # 功能模块
                self.sheet1.write(j, 3, self.testcase_name)  # 用例名称√
                self.sheet1.write(j, 4, run)  # 是否冒烟
                self.sheet1.write(j, 5, self.precondition)  # 前置条件√
                self.sheet1.write(j, 6, self.testcase_step)  # 用例步骤√
                self.sheet1.write(j, 7, self.test_result)  # 预期结果√
                self.sheet1.write(j, 8, case_type)  # 用例类型
                self.sheet1.write(j, 9, case_statu)  # 用例状态
                self.sheet1.write(j, 10, case_level)  # 用例等级
                self.sheet1.write(j, 11, creator)  # 创建人
                j = j + 1
        test_xls_name = str(excel_path) + self.xls_name + '.xlsx'
        self.f.save(test_xls_name)  # excel名称取xmind主题名称
        print('-------------------共生成用例:%d条，保存于"%s"------------------' % (j - 1, test_xls_name))
        return test_xls_name
    def xls_to_xlsx(self, fname):
        '''
        xls格式转xlsx（会死锁，弃用）
        :param fname:
        :return:
        '''
        excel = win32.gencache.EnsureDispatch('Excel.Application')
        wb = excel.Workbooks.Open(fname)
        wb.SaveAs(fname + "x", FileFormat=51)  # FileFormat = 51 is for .xlsx extension
        wb.Close()  # FileFormat = 56 is for .xls extension
        excel.Application.Quit()
    def modify_excel(self, test_xls_name, last_row=10000 ):
        '''
        美化目标表格
        :param test_xls_name: 表格的绝对路径，必填
        :param last_row: 表格的end行的行数。方便用于设置格式，行数超出有值的行时会自动匹配有值行。非必填
        :return:
        '''
        # 写入数据
        test = pd.read_excel(test_xls_name)
        # writer = pd.ExcelWriter('pandas_excel.xlsx', engine='xlsxwriter')

        writer = pd.ExcelWriter(test_xls_name, engine='xlsxwriter')
        test.to_excel(writer, sheet_name='TestCases', index=False)
        # 创建处理对象，指定数据所在的sheet
        workbook = writer.book
        worksheet = writer.sheets['TestCases']
        fmt = workbook.add_format({"font_name": u"微软雅黑"})
        backfmt = workbook.add_format({'bg_color': '#00FFFF'})
        # 设置列宽
        worksheet.set_column('A:A', 8, fmt)
        worksheet.set_column('B:B', 15, fmt)
        worksheet.set_column('C:C', 9, fmt)
        worksheet.set_column('D:D', 12, fmt)
        worksheet.set_column('E:E', 8, fmt)
        worksheet.set_column('F:H', 40, fmt)
        worksheet.set_column('I:L', 10, fmt)
        border_format = workbook.add_format({'border': 1})
        worksheet.conditional_format('A1:L%d' % last_row, {'type': 'no_blanks', 'format': border_format})
        worksheet.conditional_format('A1:L1', {'type': 'no_blanks', 'format': backfmt})

        writer.save()
    def v_path_exist(self, path_name='', path_example=''):
        '''
        返回存在的目录或者文件路径
        :param path_name: 用于提示语，xx文件，非必填
        :param path_example:用于提示语，输入示例，非必填
        :return: 校验通过后的目录或文件路径
        '''
        v_path = Path(input("请输入%s的路径，例如：%s  \n" % (path_name, path_example)))
        i = 1
        while i:
            if v_path.exists():  # 指定的目录存在
                i = 0
            else:
                v_path = Path(input("%s的路径不存在，请重新输入！例如：“%s”。 \n" % (path_name, path_example)))
        return v_path

if __name__ == '__main__':
    # # 输入xmind文件路径和excel保存路径
    # xmind_file = xmind_to_excel().v_path_exist("xmind文件", "D:\中心主题.xmind ")
    # excel_path = xmind_to_excel().v_path_exist("excel保存文件", "D:\ ")
    # xmind_file = "D:\\中心主题.xmind"  # xmind文件
    # excel_path = "D:\ "

    '''打开选择文件对话框'''
    root = tk.Tk()
    root.withdraw()
    xmind_file = filedialog.askopenfilename(title="选择xmind导图", filetypes=(("xmind", "*.xmind"),))  # 获得选择好的文件
    while xmind_file.strip() == '':
        xmind_file = filedialog.askopenfilename(title="选择xmind导图", filetypes=(("xmind", "*.xmind"),))
    excel_path = filedialog.askdirectory(title="选择excel保存位置")  # 获得选择好的文件夹
    while excel_path.strip() == '':
        excel_path = filedialog.askdirectory(title="选择excel保存位置")
    if excel_path[-1] != '/':
        excel_path += '/'

    '''部分固定列值定义'''
    case_list = '派平台-阿里商旅'  # 用例目录1
    function_module = '功能模块'   # 功能模块2
    creator = '创建人'             # 创建人11

    '''xmind生成excel'''
    try:
        test_xls_name = xmind_to_excel().write_excel(xmind_file, creator, function_module, case_list, excel_path)
    except:
        print(">>>error.选择的路径无权限操作,程序异常中止!")
        sys.exit()

    '''excel格式调整'''
    last_row = 20000   # 最大行
    xmind_to_excel().modify_excel(test_xls_name, last_row)
    if_open_message = "测试用例保存于："+ test_xls_name +"\n是否打开保存路径?"
    if_open = messagebox.askokcancel(title="提示", message=if_open_message)
    print(">>>是否打开保存路径?", if_open)
    if if_open:
        os.startfile(excel_path)

